package com.prism.moodme.datastore;

import com.prism.moodme.app.MainFrame;
import com.prism.moodme.data.Data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Scanner;
import java.util.StringTokenizer;
import java.lang.String;
import java.net.URL;

public class AccessDataStore {
	private static String dbURL = "jdbc:derby:myDB;create=true;user=me;password=mine";
	private static String tableName="data";
	private static Connection conn = null;
    private static Statement stmt = null;
//	private static String filePath="./Moodme beta/src/com/prism/moodme/datastore/sheet";
	public static URL url = AccessDataStore.class.getResource("/com/prism/moodme/datastore/sheet");

	private static void createConnection()
    {
		if (conn!=null) return;
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL);
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }

    public static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
                stmt=null;
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
                conn=null;
            }
        }
        catch (SQLException sqlExcept)
        {

        }

    }

	public static int uploadToDataStore() throws FileNotFoundException, SQLException{
		createConnection();
		Scanner scan;
		try {
			scan = new Scanner(new InputStreamReader(url.openStream()));
			while (scan.hasNextLine()) {
				String line=scan.nextLine();
				StringTokenizer tokens=new StringTokenizer(line, "\n\t");
				if (tokens.countTokens()<12) {System.out.println("error parsing"); continue;}
				String username=tokens.nextToken();
	            int condition=Integer.parseInt(tokens.nextToken());
	            long time=Long.valueOf(tokens.nextToken());
	            double features[]=new double[9];
	            for (int i=0; i<9; i++) {
	            	features[i]=Double.parseDouble(tokens.nextToken());
	            }

	            // TODO current workaround on GPS
	    		double gpsLat = GeoGenerator.randn_trig(30.601389,0.01);
	    		double gpsLong = GeoGenerator.randn_trig(-96.314444,0.01);
	    		Data data=new Data(username,condition,features,gpsLong,gpsLat,time);
	    		String dataID=data.getDataID();
	    		stmt = conn.createStatement();
	            stmt.execute("insert into " + tableName
	            		                    + " (userName,mood,feature0,feature1,feature2,feature3,feature4,feature5,feature6,feature7,feature8,dataID,time,gpsLong,gpsLat) "
	            		                    +"values ('"+username+"',"+condition+","
	            		                    +features[0]+","+features[1]+","+features[2]+","+features[3]+","+features[4]+","+features[5]+","+features[6]+","+features[7]+","+features[8]+",'"
	            		                    +dataID+"',"+time+","+gpsLong+","+gpsLat+")");
	            stmt.close();


			}
			scan.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 0;
	}

	public static ArrayList<Data> queryAll() throws SQLException {
		ArrayList<Data> resultList=new ArrayList<Data>();
		createConnection();
		stmt = conn.createStatement();
        ResultSet results = stmt.executeQuery("select * from " + tableName);
        while (results.next()) {
        	String userName=results.getString(2);
        	int mood=results.getInt(3);
        	double[] features=new double[9];
        	for (int i=0; i<9; i++) {
        		features[i]=results.getDouble(i+4);
        	}
        	String dataID=results.getString(13);
        	boolean isCentroid=results.getBoolean(14);
        	long time=results.getLong(15);
        	double gpsLong=results.getDouble(16);
        	double gpsLat=results.getDouble(17);
        	Data data=new Data(userName,mood,features,gpsLong,gpsLat,time);
        	data.setDataID(dataID);
        	data.setCentroid(isCentroid);
        	resultList.add(data);
        }
        stmt.close();
		return resultList;
	}
	public static ArrayList<Data> query(String user) throws SQLException {
		ArrayList<Data> resultList=new ArrayList<Data>();
		createConnection();
		stmt = conn.createStatement();
        ResultSet results = stmt.executeQuery("select * from " + tableName+" where userName='"+user+"'");
        while (results.next()) {
        	String userName=results.getString(2);
        	int mood=results.getInt(3);
        	double[] features=new double[9];
        	for (int i=0; i<9; i++) {
        		features[i]=results.getDouble(i+4);
        	}
        	String dataID=results.getString(13);
        	boolean isCentroid=results.getBoolean(14);
        	long time=results.getLong(15);
        	double gpsLong=results.getDouble(16);
        	double gpsLat=results.getDouble(17);
        	Data data=new Data(userName,mood,features,gpsLong,gpsLat,time);
        	data.setDataID(dataID);
        	data.setCentroid(isCentroid);
        	resultList.add(data);
        }
        stmt.close();
		return resultList;
	}
	public static int cleanup(String user) throws SQLException {
		createConnection();
		stmt=conn.createStatement();
		stmt.executeUpdate("delete from "+tableName+ "where userName='"+user+"'");
		stmt.close();
		return 0;
	}

	public static int cleanupAll() throws SQLException {
		createConnection();
		stmt=conn.createStatement();
		stmt.executeUpdate("delete from "+tableName);
		stmt.close();
		return 0;
	}

	public static ArrayList<Data> getData(String user, int condition) throws SQLException {
		ArrayList<Data> resultList=new ArrayList<Data>();
		createConnection();
		stmt = conn.createStatement();
        ResultSet results = stmt.executeQuery("select * from " + tableName+" where userName='"+user+"' and mood="+condition);
        while (results.next()) {
        	String userName=results.getString(2);
        	int mood=results.getInt(3);
        	double[] features=new double[9];
        	for (int i=0; i<9; i++) {
        		features[i]=results.getDouble(i+4);
        	}
        	String dataID=results.getString(13);
        	boolean isCentroid=results.getBoolean(14);
        	long time=results.getLong(15);
        	double gpsLong=results.getDouble(16);
        	double gpsLat=results.getDouble(17);
        	Data data=new Data(userName,mood,features,gpsLong,gpsLat,time);
        	data.setDataID(dataID);
        	data.setCentroid(isCentroid);
        	resultList.add(data);
        }
        stmt.close();
		return resultList;
	}

	public static Data getData(String user, long time) throws SQLException {
		ArrayList<Data> resultList=new ArrayList<Data>();
		createConnection();
		stmt = conn.createStatement();
        ResultSet results = stmt.executeQuery("select * from " + tableName+" where userName='"+user+"' and time="+time);
        while (results.next()) {
        	String userName=results.getString(2);
        	int mood=results.getInt(3);
        	double[] features=new double[9];
        	for (int i=0; i<9; i++) {
        		features[i]=results.getDouble(i+4);
        	}
        	String dataID=results.getString(13);
        	boolean isCentroid=results.getBoolean(14);
        	double gpsLong=results.getDouble(16);
        	double gpsLat=results.getDouble(17);
        	Data data=new Data(userName,mood,features,gpsLong,gpsLat,time);
        	data.setDataID(dataID);
        	data.setCentroid(isCentroid);
        	resultList.add(data);
        }
        stmt.close();
        if (resultList.size()==0) return null;
		return resultList.get(0);
	}

	public static ArrayList<Data> getCentroid(String user,int condition) throws SQLException {
		ArrayList<Data> resultList=new ArrayList<Data>();
		createConnection();
		stmt = conn.createStatement();
        ResultSet results = stmt.executeQuery("select * from " + tableName+" where userName='"+user
        		                                               +"' and mood="+condition+" and isCentroid="+true);
        while (results.next()) {
        	String userName=results.getString(2);
        	int mood=results.getInt(3);
        	double[] features=new double[9];
        	for (int i=0; i<9; i++) {
        		features[i]=results.getDouble(i+4);
        	}
        	String dataID=results.getString(13);
        	boolean isCentroid=results.getBoolean(14);
        	long time=results.getLong(15);
        	double gpsLong=results.getDouble(16);
        	double gpsLat=results.getDouble(17);
        	Data data=new Data(userName,mood,features,gpsLong,gpsLat,time);
        	data.setDataID(dataID);
        	data.setCentroid(isCentroid);
        	resultList.add(data);
        }
        stmt.close();
        if (resultList.size()==0) return null;
		return resultList;
	}
	/**
	 * Return value indicates whether the object is updated or added or abnormal situation occurs
	 * 0: updated
	 * 1: added
	 * -1: more than one object found, no change was made
	 * @throws SQLException
	 */
	public static int updateData(Data object) throws SQLException {
		createConnection();
		stmt = conn.createStatement();
		ResultSet results = stmt.executeQuery("select * from " + tableName+" where dataID='"+object.getDataID()+"'");
		int count=0;
		while (results.next()) count++;
		if (count==0) {
			stmt.execute("insert into " + tableName
                    + " (userName,mood,feature0,feature1,feature2,feature3,feature4,feature5,feature6,feature7,feature8,dataID,isCentroid,time,gpsLong,gpsLat) "
                    +"values ('"+object.getUser()+"',"+object.getCondition()+","
                    +object.getFeatures().get(0)+","+object.getFeatures().get(1)+","+object.getFeatures().get(2)+","+object.getFeatures().get(3)
                    +","+object.getFeatures().get(4)+","+object.getFeatures().get(5)+","+object.getFeatures().get(6)+","+object.getFeatures().get(7)
                    +","+object.getFeatures().get(8)+",'"+object.getDataID()+"',"+object.isCentroid()+","+object.getTime()+","+object.getGpsLong()+","+object.getGpsLat()+")");
            stmt.close();
			return 1;
		}
		if (count==1) {
			stmt.executeUpdate("update "+tableName+" set userName='"+ object.getUser()+"',mood="+object.getCondition()+",feature0="+object.getFeatures().get(0)
					                    +",feature1="+object.getFeatures().get(1)+",feature2="+object.getFeatures().get(2)
					                    +",feature3="+object.getFeatures().get(3)+",feature4="+object.getFeatures().get(4)
					                    +",feature5="+object.getFeatures().get(5)+",feature6="+object.getFeatures().get(6)
					                    +",feature7="+object.getFeatures().get(7)+",feature8="+object.getFeatures().get(8)
					                    +",isCentroid="+object.isCentroid()+",time="+object.getTime()
					                    +",gpsLong="+object.getGpsLong()+",gpsLat"+object.getGpsLat()+" where dataID="+object.getDataID());
			stmt.close();
			return 0;
		}
		return -1;
	}
	public static void main(String args[]) {

		try {
		
			createConnection();
			PreparedStatement ps = conn.prepareStatement("set schema ?");
			ps.setString(1,"APP");
			ps.executeUpdate();
            stmt = conn.createStatement();
	        ResultSet results =stmt.executeQuery("select * from " + tableName);	
	        if (results.next()) {
	        	System.out.println("data exist");
	        } else {
	        	uploadToDataStore();
	        	System.out.println("data uploaded");
	        }
	        stmt.close();
	       
		} catch (SQLException e) {
			try {
				stmt = conn.createStatement();
				stmt.execute("create table data (id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, userName VARCHAR(100), mood INTEGER, feature0 DOUBLE, feature1 DOUBLE, feature2 DOUBLE, feature3 DOUBLE, feature4 DOUBLE, feature5 DOUBLE, feature6 DOUBLE, feature7 DOUBLE, feature8 DOUBLE, dataID VARCHAR(400), isCentroid BOOLEAN DEFAULT false, time BIGINT, gpsLong DOUBLE, gpsLat DOUBLE)");
				uploadToDataStore();
	            stmt = conn.createStatement();
		        ResultSet results =stmt.executeQuery("select * from " + tableName);	
		        if (results.next()) {
		        	System.out.println("passed");
		        } else {
		        	System.out.println("failed");
		        }
		        stmt.close();
			} catch (SQLException e1) {
				System.out.println("nested exception");
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
            
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		MainFrame main = new MainFrame();
		main.setVisible(true);

	}

}
